/**
 * @(#) ExcelUtil.java      1.0  2018年7月2日  hunter
 * <p>
 * Copyright  (c)  2018 	keyway. All Rights Reserved.
 */
package com.wyd.common.util;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.Serializable;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

/**
 * Excel 读取工具类
 */
@Slf4j
public class ExcelUtils {

    /**
     * 通过指定路径获取工作簿
     *
     * @param path Excel 路径
     * @author hunter
     * @since 2018年7月3日 上午9:03:35
     */
    public static Workbook getNetworkExcel(String path) throws IOException, InvalidFormatException {
        return WorkbookFactory.create(new File(path));
    }

    /**
     *  获取某个sheet某行数据 --- 用于标题获取校验   从0开始计数
     * @param workbook
     * @param sheetNum  属于那个sheet ,
     * @param rowNum     获取哪一行
     * @return
     */
    public  List<String> getTitle(Workbook workbook , Integer sheetNum , Integer rowNum) {
        List<String> title = new ArrayList<>();
        Sheet sheet = workbook.getSheetAt(sheetNum);
        Row row = sheet.getRow(rowNum);
        if (row != null) {
            // 获取有数据的列数
            int columns = row.getLastCellNum();
            for (int j = 0; j < columns; j++) {
                String val = getVal(row.getCell(j));
                if (val != null && !"".equals(val)){
                    title.add(val);
                }
            }
        }
        return title;
    }

    /**
     * 获取头部信息
     *
     * @param row
     * @return
     */
    private List<String> getHead(Row row) {
        List<String> head = new ArrayList<>();
        int columns = row.getLastCellNum();
        for (int j = 0; j < columns; j++) {
            Cell cell = row.getCell(j);
            if(cell!=null){
                String value = cell.getStringCellValue();
                head.add(value);
            }
        }
        return head;
    }

    /**
     * 判断单元格类型获取数据
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell){
        String value;
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                //如果为时间格式的内容
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    value=sdf.format(HSSFDateUtil.getJavaDate(cell.
                            getNumericCellValue()));
                    break;
                } else {
                    value = new DecimalFormat("#.#").format(cell.getNumericCellValue());
                }
                break;
            case HSSFCell.CELL_TYPE_STRING: // 字符串
                value = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                value = cell.getBooleanCellValue() + "";
                break;
            case HSSFCell.CELL_TYPE_FORMULA: // 公式
                value = cell.getCellFormula() + "";
                break;
            case HSSFCell.CELL_TYPE_BLANK: // 空值
                value = "";
                break;
            case HSSFCell.CELL_TYPE_ERROR: // 故障
                value = "非法字符";
                break;
            default:
                value = "未知类型";
                break;
        }
        return value;
    }

    /**
     * 判断字符串是否是数字
     * @param str
     * @return
     */
    public static boolean isNumeric(String str){
        Pattern pattern = Pattern.compile("[0-9]*");
        return pattern.matcher(str).matches();
    }

    /**
     * 获取头菜单开始行
     *
     * @param sheet
     * @return
     */
    private static int getContentRow(Sheet sheet) {
        int rows = sheet.getLastRowNum();
        for (int i = 0; i <= rows; i++) {
            // 获取当前行数据
            Row row = sheet.getRow(i);
            if (row != null) {
                int columns = row.getLastCellNum();
                for (int j = 0; j < columns; j++) {
                    Cell cell = row.getCell(j);
                    if(cell!=null){
                        String value = cell.getStringCellValue();
                        if ("行次".equals(value)) {
                            return i;
                        }
                    }
                }
            }
        }
        return -1;
    }

    /**
     * 获取数据集
     *
     * @param workbook
     * @param indexs
     * @return
     */
    public Map<Integer, List<Object[]>> getItem(Workbook workbook, Integer[] indexs) {
        Map<Integer, List<Object[]>> items = new HashMap();
        Integer num = workbook.getNumberOfSheets();
        if (num != indexs.length) {
            return null;
        }
        for (int a = 0; a < num; a++) {
            List<Object[]> data = new ArrayList<>();
            Sheet sheet = workbook.getSheetAt(a);
            int rows = sheet.getLastRowNum();
            // 从指定行读取数据到Excel 有内容的最有一行
            for (int i = 4; i <= rows - 2; i++) {
                // 获取当前行数据
                Row row = sheet.getRow(i);
                if (row != null) {
                    // 获取有数据的列数
                    int columns = row.getLastCellNum();
                    Object[] strs = new Object[columns];
                    for (int j = 0; j < columns; j++) {
                        strs[j] = getVal(row.getCell(j));
                    }
                    data.add(strs);
                }
            }
            items.put(a, data);
        }
        return items;
    }

    /**
     * 获取安全生产和环境保护的数据集
     *
     * @param workbook
     * @param indexs
     * @return
     */
    public Map<Integer, List<Object[]>> getSafeData(Workbook workbook, Integer[] indexs) {
        Map<Integer, List<Object[]>> items = new HashMap();
        Integer num = workbook.getNumberOfSheets();
        if (num != indexs.length) {
            return null;
        }
        for (int a = 0; a < num; a++) {
            List<Object[]> data = new ArrayList<>();
            Sheet sheet = workbook.getSheetAt(a);
            int rows = sheet.getLastRowNum();
            // 从指定行读取数据到Excel 有内容的最有一行
            for (int i = 2; i <= rows; i++) {
                // 获取当前行数据
                Row row = sheet.getRow(i);
                if (row != null) {
                    int temp = 0;
                    // 获取有数据的列数
                    int columns = row.getLastCellNum();
                    Object[] strs = new Object[columns];
                    for (int j = 0; j < columns; j++) {
                        if (j == 1){
                            if (getVal(row.getCell(j)).isEmpty()){
                                temp = 1;
                                break;
                            }
                        }
                        strs[j] = getVal(row.getCell(j));
                    }
                    if (temp == 0){
                        data.add(strs);
                    }
                }
            }
            items.put(a, data);
        }
        return items;
    }

    /**
     * 获取企业分类上传数据集
     *
     * @param workbook
     * @param indexs
     * @return
     */
    public Map<Integer, List<Object[]>> getCompanyItem(Workbook workbook, Integer[] indexs) {
        Map<Integer, List<Object[]>> items = new HashMap();
        Integer num = workbook.getNumberOfSheets();
        if (num != indexs.length) {
            return null;
        }
        for (int a = 0; a < num; a++) {
            List<Object[]> data = new ArrayList<>();
            Sheet sheet = workbook.getSheetAt(a);
            int rows = sheet.getLastRowNum();
            // 从指定行读取数据到Excel 有内容的最有一行
            for (int i = 3; i <= rows - 2; i++) {
                // 获取当前行数据
                Row row = sheet.getRow(i);
                if (row != null) {
                    // 获取有数据的列数(减去序号列)
                    int columns = row.getLastCellNum();
                    Object[] strs = new Object[columns];
                    for (int j = 0; j < columns; j++) {
                        if(j==0){
                            continue;
                        }
                        strs[j] = getVal(row.getCell(j));
                    }
                    if(StringUtils.isNotEmpty(strs[1]+"")){
                        //企业名字不为空的才加入
                        data.add(strs);
                    }
                }
            }
            items.put(a, data);
        }
        return items;
    }

    /**
     * 获取单元格数据
     *
     * @param cell Excel 单元格
     * @author hunter
     * @since 2018年7月3日 上午9:05:01
     */
    private String getVal(Cell cell) {
        if (cell != null) {
            // 先把单元格类型强制设置为 String
            cell.setCellType(Cell.CELL_TYPE_STRING);
            return cell.getStringCellValue();
        }
        return null;
    }

    /**
     * 读取excel所有数据 返回有序map
     * sheetName 截取最大长度31
     * 时间格式：yyyy-MM-dd HH:mm:ss SSS
     */
    public static Map<String, List<List<String>>> readExcel(String path) throws IOException, InvalidFormatException {
        return readExcel(getNetworkExcel(path));
    }

    /**
     * 读取excel所有数据 返回有序map
     * sheetName 截取最大长度31
     * 时间格式：yyyy-MM-dd HH:mm:ss SSS
     */
    public static Map<String, List<List<String>>> readExcel(Workbook workbook) {
        if (workbook != null) {
            Map<String, List<List<String>>> items = new LinkedHashMap<>();

            FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();

            Iterator<Sheet> sheetIterator = workbook.sheetIterator();
            while (sheetIterator.hasNext()) {
                Sheet sheet = sheetIterator.next();

                String sheetName = getSheetName(sheet);

                items.put(sheetName, readSheet(sheet, formulaEvaluator));
            }

            try {
                workbook.close();
            } catch (IOException e) {
                log.error("关闭workbook错误", e);
            }

            return items;
        }

        return null;
    }

    /**
     * 获取sheetName
     * 对长度超过31的进行截断处理
     * @param sheet
     * @return
     */
    private static String getSheetName(Sheet sheet) {
        String sheetName = sheet.getSheetName();
        if (sheetName.length() > 31) {
            log.error("遇到了sheetName超过31的情况 截取0-31 originalSheetName={}", sheet.getSheetName());
            sheetName = sheetName.substring(0, 31);
        }

        return sheetName;
    }

    /**
     * 读取excel所有数据 返回有序集合
     * sheetName 截取最大长度31
     * 时间格式：yyyy-MM-dd HH:mm:ss SSS
     */
    public static List<SheetBean> readExcelGetSheetBeanList(Workbook workbook) {
        if (workbook != null) {
            List<SheetBean> items = new ArrayList<>();

            FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();

            Iterator<Sheet> sheetIterator = workbook.sheetIterator();
            while (sheetIterator.hasNext()) {
                Sheet sheet = sheetIterator.next();

                String sheetName = getSheetName(sheet);

                items.add(new SheetBean(sheetName, readSheet(sheet, formulaEvaluator)));
            }

            try {
                workbook.close();
            } catch (IOException e) {
                log.error("关闭workbook错误", e);
            }

            return items;
        }

        return null;
    }

    /**
     * 读取excel默认sheet的所有数据
     * 时间格式：yyyy-MM-dd HH:mm:ss SSS
     */
    public static List<List<String>> readExcelDefaultSheet(String path) throws IOException, InvalidFormatException {
        return readExcelDefaultSheet(getNetworkExcel(path));
    }

    /**
     * 读取excel默认sheet的所有数据
     * 时间格式：yyyy-MM-dd HH:mm:ss SSS
     */
    public static List<List<String>> readExcelDefaultSheet(Workbook workbook) {
        if (workbook != null) {
            Sheet sheet = workbook.sheetIterator().next();

            List<List<String>> lists = readSheet(sheet, workbook.getCreationHelper().createFormulaEvaluator());

            try {
                workbook.close();
            } catch (IOException e) {
                log.error("关闭workbook错误", e);
            }

            return lists;
        }

        return null;
    }

    /**
     * 读取sheet数据
     * 时间格式：yyyy-MM-dd HH:mm:ss SSS
     */
    private static List<List<String>> readSheet(Sheet sheet, FormulaEvaluator formulaEvaluator) {
        List<List<String>> data = new ArrayList<>();

        DataFormatter dataFormatter = new DataFormatter();

        sheet.forEach(row -> {
            if (row != null) {
                // -1 代表行没有任何单元格
                if (row.getLastCellNum() > -1) {
                    ArrayList<String> strs = new ArrayList<>(row.getLastCellNum());
                    // 此处循环row不能使用foreach row会忽略空列 导致数据错位
                    for (int i = 0; i < row.getLastCellNum(); i++) {
                        Cell cell = row.getCell(i);
                        if (cell == null) {
                            strs.add(null);
                            continue;
                        }

                        // 时间格式单独处理
                        if (cell.getCellTypeEnum().equals(CellType.NUMERIC) && DateUtil.isCellDateFormatted(cell)) {
                            strs.add(DateFormatUtils.format(cell.getDateCellValue(), "yyyy-MM-dd HH:mm:ss SSS"));
                        } else {
                            strs.add(dataFormatter.formatCellValue(cell, formulaEvaluator));
                        }
                    }

                    data.add(strs);
                }
            }
        });

        return data;
    }

    /**
     * 将生产的Excel 文件导出 到 response
     */
    public static void exportResponse(Workbook workbook, String name, HttpServletResponse response) {
        String suffix = "";
        if (workbook instanceof XSSFWorkbook || workbook instanceof SXSSFWorkbook) {
            suffix = "xlsx";
        } else if (workbook instanceof HSSFWorkbook) {
            suffix = "xls";
        }

        BufferedOutputStream fos = null;
        try {
            String fileName = name + "." + suffix;
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));
            fos = new BufferedOutputStream(response.getOutputStream());
            workbook.write(fos);
            fos.flush();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (fos != null) {
                try {
                    fos.close();
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 检查模板数据是否在目标数据中都一样
     * sheet名称字数限制31字，但是存在超过31字的情况
     */
//    public static void checkData(Map<String, List<List<String>>> templateData, Map<String, List<List<String>>> targetData) {
//        templateData.forEach((sheetName, sheet) -> {
//            final List<List<String>> targetSheet = targetData.get(sheetName);
//            if (targetSheet == null) {
//                throw new ServerException(Constants.RETURN_INFO_CODE_ERR,
//                        "未找到sheet[%s]", sheetName);
//            }
//            if (targetSheet.size() < sheet.size()) {
//                throw new ServerException(Constants.RETURN_INFO_CODE_ERR,
//                        "sheet[%s]格式有误", sheetName);
//            }
//
//            for (int i = 0; i < sheet.size(); i++) {
//                final List<String> row = sheet.get(i); // 某一行
//                final List<String> targetRow = targetSheet.get(i);
//                if (targetRow.size() < row.size()) {
//                    throw new ServerException(Constants.RETURN_INFO_CODE_ERR,
//                            "sheet[%s]第[%s]行格式有误", sheetName, i + 1);
//                }
//
//                for (int j = 0; j < row.size(); j++) {
//                    final String cell = row.get(j);
//                    if (StringUtils.isNotBlank(cell)) {
//                        // 非空才进行比较
//                        if (!cell.equals(targetRow.get(j))) {
//                            throw new ServerException(Constants.RETURN_INFO_CODE_ERR,
//                                    "sheet[%s]第[%s]行，第[%s]列与模板不对应", sheetName, i + 1, j + 1);
//                        }
//                    }
//                }
//            }
//        });
//    }

    /**
     * excel sheet 封装
     */
    @AllArgsConstructor
    @NoArgsConstructor
    @Data
    public static class SheetBean implements Serializable {
        private static final long serialVersionUID = -4317760787493068181L;

        /**
         * 名称
         */
        private String name;

        /**
         * 二维集合 对应excel数据
         */
        private List<List<String>> data;
    }
}
